﻿/*
************************************************************
CreateDate  *  Author        *  Content
2008-07-04  *  Donne.Yang  *  Query data and execute data
************************************************************
NO   *  UpdateDate   *  Author        *  Content

*************************************************************
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Configuration;
using System.Xml;

namespace Stc.Dbo
{
    public class OracleDBOperation
    {
        private string strConn;//DB

        public OracleDBOperation()
        {
            if (Configuration.OracleConn == null)
            {
                this.strConn = ConfigurationSettings.AppSettings["OracleConn"];
                Configuration.OracleConn = ConfigurationSettings.AppSettings["OracleConn"];
            }
            else
            {
                this.strConn = Configuration.OracleConn;
            }
        }

        public OracleDBOperation(string strConn)
        {
            this.strConn = strConn;
        }

        /// <summary>
        /// Connect Oracle DB 
        /// </summary>
        /// <returns>Oracle DB Connection</returns>
        private OracleConnection GetConnection()
        {
            OracleConnection oracleConnection = null;
            try
            {
                oracleConnection = new OracleConnection(strConn);
                oracleConnection.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return oracleConnection;
        }

        /// <summary>
        /// Close Oracle DB Connection
        /// </summary>
        /// <param name="oracleConnection">OracleConnection</param>
        private void CloseDB(OracleConnection oracleConnection)
        {
            if (oracleConnection.State != ConnectionState.Closed)
            {
                try
                {
                    oracleConnection.Close();
                    oracleConnection.Dispose();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// Query data from oracle DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>data with dataset format</returns>
        public DataSet GetResult(string strSql)
        {
            OracleConnection oracleConnection = GetConnection();

            DataSet dsData = new DataSet();

            try
            {
                OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();

                oracleDataAdapter.SelectCommand = new OracleCommand(strSql, oracleConnection);

                oracleDataAdapter.Fill(dsData);
                oracleDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(oracleConnection);
            }

            return dsData;
        }

        /// <summary>
        /// Query data from oracle DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>data with datareader format</returns>
        public OracleDataReader GetResultRead(string strSql)
        {
            OracleConnection oracleConnection = GetConnection();

            OracleDataReader oracleDataReader = null;

            try
            {
                OracleCommand oracleCommand = new OracleCommand(strSql, oracleConnection);

                oracleDataReader = oracleCommand.ExecuteReader();
                oracleCommand.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(oracleConnection);
            }

            return oracleDataReader;
        }

        /// <summary>
        /// update data of oracle DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>execute state with ture or false</returns>
        public bool UpdateData(string strSql)
        {
            bool blnFlag = false;

            OracleConnection oracleConnection = GetConnection();

            try
            {
                OracleCommand oracleCommand = new OracleCommand(strSql, oracleConnection);

                if (oracleCommand.ExecuteNonQuery() > 0)
                {
                    blnFlag = true;
                }
                oracleCommand.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(oracleConnection);
            }

            return blnFlag;
        }


        /// <summary>
        /// update data of oracle DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>execute state with ture or false</returns>
        public bool UpdateData(string strSql, OracleParameter[] Parameters)
        {
            bool blnFlag = false;
            int num = 0;

            OracleConnection oracleConnection = GetConnection();

            try
            {
                OracleCommand oracleCommand = oracleConnection.CreateCommand();
                oracleCommand.CommandText = strSql;
                oracleCommand.CommandType = CommandType.Text;
                oracleCommand.Parameters.AddRange(Parameters);

                num = oracleCommand.ExecuteNonQuery();
                oracleCommand.Parameters.Clear();
                oracleCommand.Dispose();

                if (num > 0)
                {
                    blnFlag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(oracleConnection);
            }

            return blnFlag;
        }

        /// <summary>
        /// Execute multiple sql sentences of update orcale DB
        /// </summary>
        /// <param name="arrSqls">sql sentences</param>
        /// <returns>execute state with ture or false</returns>
        public bool ExecuteTransaction(ArrayList arrSqls)
        {
            bool blnFlag = false;

            OracleConnection oracleConnection = GetConnection();

            OracleTransaction oracleTransaction = oracleConnection.BeginTransaction();
            OracleCommand oracleCommand = oracleConnection.CreateCommand();
            oracleCommand.Transaction = oracleTransaction;

            try
            {
                OracleString rowId;

                for (int i = 0; i < arrSqls.Count; i++)
                {
                    oracleCommand.CommandText = (string)arrSqls[i];
                    oracleCommand.ExecuteOracleNonQuery(out rowId);
                }

                oracleTransaction.Commit();

                blnFlag = true;
            }
            catch (Exception ex)
            {
                try
                {
                    oracleTransaction.Rollback();
                }
                catch (Exception ex2)
                {
                    if (oracleTransaction.Connection != null)
                    {
                        throw ex2;
                    }
                }
                throw ex;
            }
            finally
            {
                oracleCommand.Dispose();
                CloseDB(oracleConnection);
            }

            return blnFlag;
        }
    }
}
